我的SQL學習資源為https://www.w3schools.com/sql
新手卡關基本都會找到說明的好去處，
建議快速閱覽一次再刷題才會有一些查詢方向。

1873. Calculate Special Bonus

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
| salary      | int     |
+-------------+---------+
employee_id is the primary key for this table.
Each row of this table indicates the employee ID, employee name, and salary.

Write an SQL query to calculate the bonus of each employee. The bonus of an employee is 100% of their salary if the ID of the employee is an odd number and the employee name does not start with the character 'M'. The bonus of an employee is 0 otherwise.

Return the result table ordered by employee_id.

The query result format is in the following example.

Example 1:

Input: 
Employees table:
+-------------+---------+--------+
| employee_id | name    | salary |
+-------------+---------+--------+
| 2           | Meir    | 3000   |
| 3           | Michael | 3800   |
| 7           | Addilyn | 7400   |
| 8           | Juan    | 6100   |
| 9           | Kannon  | 7700   |
+-------------+---------+--------+
Output: 
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 2           | 0     |
| 3           | 0     |
| 7           | 7400  |
| 8           | 0     |
| 9           | 7700  |
+-------------+-------+
Explanation: 
The employees with IDs 2 and 8 get 0 bonus because they have an even employee_id.
The employee with ID 3 gets 0 bonus because their name starts with 'M'.
The rest of the employees get a 100% bonus.

這一題要我們將ID為奇數且名字不為'M'的員工bonus維持100%，其他則歸0
，一開始想說用WHERE employee_id % 2 != 0 AND name NOT LIKE 'M%'去判斷，
但不清楚歸0的If怎麼寫，於是這一題拜託了GOOGLE大神，
這邊要注意LeetCode Submit時會做測試，一定要寫ORDER BY才不會又遇到Run ok 卻Submit失敗的情況。

SELECT employee_id, 
      (if(employee_id % 2 != 0 AND 
      name NOT LIKE 'M%', salary, 0)) AS bonus  /*網路上另有case when的寫法*/
FROM Employees
ORDER BY employee_id;                           /*需要依employee_id才能過LeetCode*/

1873. Calculate Special Bonus

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| name        | varchar  |
| sex         | ENUM     |
| salary      | int      |
+-------------+----------+
id is the primary key for this table.
The sex column is ENUM value of type ('m', 'f').
The table contains information about an employee.

Write an SQL query to swap all 'f' and 'm' values (i.e., change all 'f' values to 'm' and vice versa) with a single update statement and no intermediate temporary tables.

Note that you must write a single update statement, do not write any select statement for this problem.

The query result format is in the following example.

Example 1:

Input: 
Salary table:
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |
+----+------+-----+--------+
Output: 
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |
+----+------+-----+--------+
Explanation: 
(1, A) and (3, C) were changed from 'm' to 'f'.
(2, B) and (4, D) were changed from 'f' to 'm'.

題目要我們將做性別調換，也就是把sex欄位的'f'、'm'兩字互轉，
下面我嘗試了上一題查到的case when用法，
另外找到了更短的寫法就一併附上。

/*
UPDATE Salary  
SET sex = CASE WHEN sex = 'm' 
THEN 'f' 
ELSE 'm' 
END;
*/

UPDATE Salary  
SET sex = if(sex='m','f','m')  /*If sex欄='m'then改成'f', 不然else 'm'*/

196. Delete Duplicate Emails

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.
 
Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest id. Note that you are supposed to write a DELETE statement and not a SELECT one.

After running your script, the answer shown is the Person table. The driver will first compile and run your piece of code and then show the Person table. The final order of the Person table does not matter.

The query result format is in the following example.
 
Example 1:

Input: 
Person table:
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Output: 
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
Explanation: john@example.com is repeated two times. We keep the row with the smallest Id = 1.

這題需要刪除重複的EMAIL並保留最小ID，題目有提示not a SELECT，
所以會先排除DISTINCT的用法，並到w3school看一下DELTEL的用法，
得知刪除重複值的做法可以產生2個一樣的表P1.P2，
再進一步比較EMAIL相同，與ID的大小，
由於腦袋到這有些打結，糾結在P1.id>P2.id的比法原理(為什麼P1>P2?)，
後來先說服自己他是從上往下去做比對，不知道這樣理解的方向是不是有誤。

DELETE P1
FROM Person P1 ,Person P2    /*寫Person AS P1也行，AS不一定要寫*/

WHERE P1.email=P2.email AND /*比較EMAIL跟ID大小*/
        P1.id>P2.id;

**DAY02